Datetime conversion

Question

Datetime conversion

viresh29

Ten Centuries

Points: 1111
More actions
May 23, 2014 at 7:22 am

#289152

Hello Folks,
I am using SSIS to load raw files into database. In my files I have columns Date which has format
1/1/2010 12:00:00 PM.
I want to load this column in format 1/1/2010 24:00:00. I mean in 24 hour format.
Any thoughts on this will be appreciated.
Thanks:-)
Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

viresh29 (5/23/2014)
Hello Folks,
I am using SSIS to load raw files into database. In my files I have columns Date which has format
1/1/2010 12:00:00 PM.
I want to load this column in format 1/1/2010 24:00:00. I mean in 24 hour format.
Any thoughts on this will be appreciated.

Date columns don't have a format. They always store data with 24 hour accuracy.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

viresh29 Ten Centuries Points: 1111 More actions · Answer 2

Thanks Sean for your reply.

the raw files which has columns CreationDate, StartDate and EndDate are by default in string. Which has format 1/1/2010 12:00:00 PM.

Now I want to load this column as in date format.

FYI- I am using PDW destination which has data types datetime and datetime2. which stores date in 24 hrs format only.

Thanks:-)

Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

viresh29 Ten Centuries Points: 1111 More actions · Answer 3

select convert(char(20),cast('1/1/2010 8:30 PM' as datetime),120)

Result: 2010-01-01 20:30:00

I can use this query in SSMS but I dont know how to use this query in SSIS.Which convert AM/PM format in 24 hr format.

Thanks:-)

Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

As Sean said, datetime data types don't have format. If you define your column type in your flat file connection, and insert the value in a datetime column, you won't have any problems.

I just tested that. If you have problems, regional settings might be causing them.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Luis Cazares (5/23/2014)
As Sean said, datetime data types don't have format. If you define your column type in your flat file connection, and insert the value in a datetime column, you won't have any problems.
I just tested that. If you have problems, regional settings might be causing them.

It might also be sensible to use datetime2 rather than datetime. That won't eliminate problems caused by regional differences - eg MDY vs DMY format in the source data - but it does simplify handling the resulting data once you've fixed those.

Tom