SQL Server Performance Forum – Threads Archive

Date Storing in Table

Can u please tell me how to store date in DD-MM-YYYY in SQL Table. i.e "Apr-01-2006" to 01-04-2006 ? I have a source that need to be upload to SQL server using DTS.

---

First of all Make a look up table for 12 months jan – 01
feb – 02
mar – 03 etc…. Then update that apr to 04
jan to 01 referencing that look up table. Then using this three type of string function divide that date into three part and arrange it.
select right(‘Apr-01-2006’,4)
select left(‘Apr-01-2006’,patindex(‘%-%’,’Apr-01-2006′)-1)
select left(stuff( ‘Apr-01-2006’,1,patindex(‘%-%’,’Apr-01-2006′),”),2)

---

or you can update whole column from month name to month number from following commmand in stored proc. select replace(‘Apr-01-2006’ , ‘apr’ , ‘;04’ )
select replace(‘Apr-01-2006’ , ‘jan’ , ‘;01’ )
select replace(‘Apr-01-2006’ , ‘feb’ , ‘;02’ )
select replace(‘Apr-01-2006’ , ‘mar’ , ‘;03’ ) Then, using following string functions you will get (dd;mm-yy) format select stuff(stuff( ‘;04-01-2006’,1,patindex(‘%-%’,’;04-01-2006′),”),3,0,left(‘;04-01-2006’,patindex(‘%-%’,’;04-01-2006′)-1)) [here ‘;04-01-2006’= column name] then using following statement you can get desired (dd-mm-yy) select replace(’01;04-2006′,’;’,’-‘)

---

or the simplest is select substring(‘Apr-01-2006′,5,2)+’-‘+left(‘Apr-01-2006′,3)+’-‘+right(‘Apr-01-2006’,4) and then replace all months by numbers.

---

A DATETIME column in SQL Server has no special format and it doesn’t need one. You usually choose the format at the client level. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

Assuming you are inserting into a datetime column (if not, you should <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />:<br /><br />convert( datetime, replace(‘Apr-01-2006’, ‘-‘, ‘ ‘), 9 )<br /><br />should work.

---

]]>