cancel
Showing results for 
Search instead for 
Did you mean: 

MS SQL Server Date Format

Former Member
0 Kudos

Hi All,

Scenario: File->XI->DB

I'm using MS SQL Server as the target DB. For a particular date field, I'm using DateTrans (dd/mm/yyyy to YYYY-MM-DD) and also have included the attribute <b>hasQuot</b> with value 'No'. But in the target table, the dateTime field is displaying junk values like 1905-05-27 00:00:00 (for a source value of 25/09/2006). I'm not populating any time value explicitly. I tried with target format YYYY-DD-MM as well but to no avail. What is the format to be used to populate only date values to dateTime fields in MS SQL? Is there anything else I'm missing?

Thanks and Regards,

Bharath

PS: When I write a INSERT statement from the SQL Manager interface, I'm using YYYY-MM-DD format for the input and it works fine.

Message was edited by: Bharath Ram S

Accepted Solutions (0)

Answers (1)

Answers (1)

Shabarish_Nair
Active Contributor
0 Kudos

<i>What is the format to be used to populate only date values to dateTime fields in MS SQL?</i>

>>>>

http://www.karaszi.com/SQLServer/info_datetime.asp

Also ref: it might be of help to you.

http://www.thescripts.com/forum/thread536746.html

Former Member
0 Kudos

Thanks a lot Shabarish.

In my case, the proper date was displayed by disabling the <b>hasQuot</b> attribute.

To summarise,

Source - DD/MM/YYYY (xsd:string)

Target - YYYY-MM-DD (xsd:string)

I used DateTrans for the transformation. No other tweaking was required. I had initially enabled the hasQuot(='No') attribute after going through the earlier threads. Too much information I guess