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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy