Date???? - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Date????

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-02-03 : 16:35:31

I have a table1 where I have (datetime)column called update_time.
I am trying to export the whole row to a text file using SSIS.

when I right click the table return all rows, the date appears as:
2/3/2009 11:46:36 AM

when I run a query select date_time from table1, it returns
2009-02-03 11:46:36.963

When I export it using the convert function
convert(varchar,date_time,22)
I get 02/03/09 11:46:36 AM

but the user wantst the century to go into the field. to read
02/03/2009 11:46:36 AM

I don't know if there are any other masks like '22' hiding somewhere that I can use. even this '22' is not in Books Online.

Any help is welcome.

Regards
Paresh Motiwala
Boston, USA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 16:40:03

I suppose you could try 122.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-02-03 : 16:41:43

Msg 281, Level 16, State 1, Line 1
122 is not a valid style number when converting from datetime to a character string.

Regards
Paresh Motiwala
Boston, USA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 16:45:04

Then use both 101 and 108, concatenated together with a space in between.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-02-03 : 16:53:28

Thanks, Tara, that kinda worked, it chops off the AM/PM. so it looks like there is no easy way out.
Strange, the only thing that I need and the vendor supplied us is not easily handled by SQL...

Regards
Paresh Motiwala
Boston, USA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-03 : 18:23:32

No easy way?


select
	DT =
		convert(varchar(10),a.MyDate, 101)+' '+
		convert(varchar(10),a.MyDate, 108)+' '+
		case when datepart(hour,a.MyDate) < 12 then 'AM' else 'PM' end,
	a.MyDate
from
	(  --Test Data
	select MyDate = getdate()	union all
	select MyDate = dateadd(hour,12,getdate())	union all
	select MyDate = '2009-02-04 11:59:59.997'	union all
	select MyDate = '2009-02-04 12:00:00.000'	union all
	select MyDate = dateadd(hour,-6,getdate())
	) a


Results:
DT                       MyDate
------------------------ ------------------------
02/03/2009 18:22:35 PM   2009-02-03 18:22:35.953
02/04/2009 06:22:35 AM   2009-02-04 06:22:35.953
02/04/2009 11:59:59 AM   2009-02-04 11:59:59.997
02/04/2009 12:00:00 PM   2009-02-04 12:00:00.000
02/03/2009 12:22:35 PM   2009-02-03 12:22:35.953

(5 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources