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-08 : 16:41:25
|
| Okay, date formats seems to be my week area. I have a column for TxnDate with a datatype datetime, so the data looks like: 2008-06-06 17:11. I now have to change this so that the format is yyyymmddThhmmss. I tried applying what I had done in a previous topic (date format), but it can't do the conversion on a datatype datetime, so I did an alter statement, but the conversion did not come out right. Any ideas on how to do this? The code I am using now on another column is:Update tblName SET EffDate = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(20), GETDATE(), 126),' ','T'),'-',''),':',''),'.','')Now I need the same reults on a date that shows as 2008-06-06 17:11. Thanks in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-08 : 16:44:44
|
| You can't change how SQL Server stores datetime data. But you can change how it is displayed coming out of the database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
theladycoder
Starting Member
16 Posts |
Posted - 2008-08-08 : 16:47:02
|
| I need that date converted to yymmddThhmmss at the time I export the data to a pipe delimited file. If I have to do it during export, then I am not sure how to go about that. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-08 : 16:59:03
|
| Like this:DECLARE @dt datetimeSET @dt = GETDATE()SELECT REPLACE(CONVERT(varchar(10), @dt, 120), '-', '') + 'T' + REPLACE(CONVERT(varchar(8), @dt, 114), ':', '')Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
theladycoder
Starting Member
16 Posts |
Posted - 2008-08-08 : 17:04:56
|
| The thing is that I do not want to getdate(). The date already exists. I need to convert it from is current format to yyyymmddThhmmss. I just don't know how to do this of if I need to do this conversion during the export to a txt file. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-08 : 17:07:08
|
| GETDATE() is used only as an example. Change the SELECT so that it goes against your table rather than @dt. This will be used in the export as you can't change how datetime data is stored in the database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Another Date Format