July 1, 2005 at 4:16 am
Hi
I posted a problem on here yesterday and received an excelent response. However, the problem I am having has changed. I originally wanted to change all of my dates in one column from yyyy/mm/dd to dd/mm/yyyy.
This was done successfully using the following code......
Update UserName.CallLogRevenuesTEST set dateOnlyEnd = SUBSTRING( dateOnlyEnd, 9, 2) + '/' + SUBSTRING( dateOnlyEnd, 6, 2) + '/' + SUBSTRING( dateOnlyEnd, 1, 4)
However, all of the new entries in the database (and there are a lot of them) are already in the format dd/mm/yyyy. There are hundreds of entries, and half are in the format dd/mm/yyyy and the other half yyyy/mm/dd.
Is it possible to use an SQL statement that changes the entries in the format yyyy/mm/dd only to the format dd/mm/yyyy
Thanks in advance for any help
Dave
July 1, 2005 at 5:15 am
It is. You will need to build a WHERE clause that looks for the yyyy/mm/dd format. Research CHARINDEX and PATINDEX in BOL for help.
Remember TEST, TEST, TEST
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 1, 2005 at 5:30 am
I'll have a look into that thanks.
Will let you all know how I got on
July 1, 2005 at 7:03 am
Its simple enough to find out which ones are in the format yyyy/mm/dd as they appear in the results as a '1' when using the following sql statement.
SELECT CHARINDEX('2005', dateOnlyEnd) AS Expr1
FROM CallLogRevenuesTEST
But is there an if statement where I could write something like...
IF (the sql i wrote above) = 1 Then
Update UserName.CallLogRevenuesTEST set dateOnlyEnd = SUBSTRING( dateOnlyEnd, 9, 2) + '/' + SUBSTRING( dateOnlyEnd, 6, 2) + '/' + SUBSTRING( dateOnlyEnd, 1, 4)
Quite stuck here with this one
Dave
July 1, 2005 at 8:27 am
Update UserName.CallLogRevenuesTEST set dateOnlyEnd = SUBSTRING( dateOnlyEnd, 9, 2) + '/' + SUBSTRING( dateOnlyEnd, 6, 2) + '/' + SUBSTRING( dateOnlyEnd, 1, 4)
Where CHARINDEX('2005', dateOnlyEnd) = 1
July 1, 2005 at 8:46 am
Are you storing your data in a char field?
I question because datetime fields are better to use for storing this information and in a decimal format of 4 bytes (date) . 4 bytes (time) instead (8 total) of the 10 you have to use for a char or 12 for a varchar.
As well stricter enforcement happens over the data which will lead to least potential issues between the datatypes.
Then finally input matters less and you can use a familiar way to different locals. And as for showing the data you have dozens of formating options availble for displaying.
As for finding those already in yyyy/ I suggest try
WHERE columnname like '[0-9][0-9][0-9][0-9]/%'
to find those records in yyyy/ format and no worry about what year they have in them.
July 1, 2005 at 8:47 am
That worked a treat, I've been trying to search for that one for hours.
Thank you for your help
Dave
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply