Date Format Problem

[gerhardl]

Hi,

I'm importing a daily account extract into QV, each day replacing the previous day's extract. We have a funny date format used in the extract, so my script looked like this (for date field [account open date]):

DATE(DATE#( [Account open date], 'DD-MMM-YY)) as [Account open date],
month(DATE(DATE#( [Account open date], 'DD-MMM-YY))) as [Account open Month],
year(DATE(DATE#( [Account open date], 'DD-MMM-YY))) as [Account open Year],
day(DATE(DATE#( [Account open date], 'DD-MMM-YY))) as [Account open Day]

I did this for all my date fields. Today, after uploading the new extract I noticed that all my data relating to dates are messed up. Upon investigation found that our IT department decided to change all dates in the system and extracts to 'DD-MMM-YYYY HH:MM:SS'

I changed my script to this:

TimeStamp(TimeStamp#( [Account open date], 'DD-MMM-YYYY HH:MM:SS')) as [Account open date],
month(TimeStamp(TimeStamp#( [Account open date], 'DD-MMM-YYYY HH:MM:SS'))) as [Account open month],
year(TimeStamp(TimeStamp#( [Account open date], 'DD-MMM-YYYY HH:MM:SS'))) as [Account open year],
day(TimeStamp(TimeStamp#( [Account open date], 'DD-MMM-YYYY HH:MM:SS'))) as [Account open day],

Now it seems that only the data for 2010 and 2011 has been loaded. There are no dates for older years. I tried to figure out why this happened and it seemed to be because all date TIMES older than 2010 has been defaulted to 00:00:00

So for 2010/2011 all dates are correct and will be e.g. 01-APR-2010 14:15:45. But for 2005-2009 they will be e.g. 01-APR-2007 00:00:00

Any ideas on how to fix this?

I don't need the timestamps AT ALL for my QV document, so will be happy to just load the date if that is easier...

This is quite urgent, any and all help will be greatly appreciated.

Gerhard

[#2]

Hello,

to remove the timestamp, i have used this in my case :

date

(DayName([Created Time]),'MM/DD/YYYY')

Hope this helps.

[#3]

Hi

Try

Date(TimeStamp#( [Account open date], 'DD-MMM-YYYY hh:mm:ss')) as [Account open date]

change HH:MM:SS to hh:mm:ss

Regards

Anders

[gerhardl]

Hi, My internet connection was a bit funky so I only saw you guys' suggestions today.

I ended up doing the following and it worked fine:

Date(Date#( left([Account open date],11), 'DD-MMM-YYYY')) as [Account open date],

Thanks,

G