Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
theladycoder
Starting Member
16 Posts |
 Posted - 2008-08-07 : 11:58:26
|
| I used the following code to test a date format:SELECT GETDATE() AS EffDate, CAST(GETDATE() AS nvarchar(30)) AS UsingCast, CONVERT(nvarchar(23), GETDATE(), 126) AS UsingConvertTo_ISO8601GOand then applied the following:Update table1 SET EffDate =CONVERT(nvarchar(30), GETDATE(), 126) WHERE EffDate is nullI do need the format to be YYYYMMDDTHHMMSS, but how do I get it to drop the colons and hypens? I'll be exporting this data to a pipe delimited file.Thanks in advance! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-07 : 12:44:42
|
| Update table1 SET EffDate =REPLACE(REPLACE(REPLACE(CONVERT(varchar(20), GETDATE(), 126),' ','T'),'-',''),':','') WHERE EffDate is null |
 |
|
|
theladycoder
Starting Member
16 Posts |
Posted - 2008-08-07 : 14:57:08
|
| That worked! I just had to include one more Replace to drop the period at the end. Again, thanks for the fresh eyes :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-07 : 14:57:59
|
quote:
Originally posted by theladycoder
That worked! I just had to include one more Replace to drop the period at the end. Again, thanks for the fresh eyes :)
No worries...Glad that i could sort it out  |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-08 : 03:35:23
|
quote:
Originally posted by theladycoder
I used the following code to test a date format:SELECT GETDATE() AS EffDate, CAST(GETDATE() AS nvarchar(30)) AS UsingCast, CONVERT(nvarchar(23), GETDATE(), 126) AS UsingConvertTo_ISO8601GOand then applied the following:Update table1 SET EffDate =CONVERT(nvarchar(30), GETDATE(), 126) WHERE EffDate is nullI do need the format to be YYYYMMDDTHHMMSS, but how do I get it to drop the colons and hypens? I'll be exporting this data to a pipe delimited file.Thanks in advance!
Why do you want to store formatted dates in a table?MadhivananFailing to plan is Planning to fail |
|
|
|
theladycoder
Starting Member
16 Posts |
Posted - 2008-08-08 : 08:27:47
|
| The reason is because the exported file has to have that format to be accepted by our vendor's system |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-08 : 08:45:35
|
quote:
Originally posted by theladycoder
The reason is because the exported file has to have that format to be accepted by our vendor's system
Then proceed with the solution given by visakhMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Date Format