June 09, 2003
Oracle v SQL Server, Part 9
Blimey. There is, as far as I can deduce, no way of easily converting a number into a date in SQL Server. Oracle has to_date but the cupboard is bare in Microsoft's finest (and I presume in Sybase as well).
For the record, my colleague had a numerical representation of a date in an integer column (e.g. 20010410 for the 10th of April, 2001) but wanted to perform date comparisons on it. Thanks to the power of the interweb we came to a solution via Experts Exchange. Our solution was slightly different and was along the lines of;
SELECT convert(datetime,
substring(cast(IntDateEntered as char(10)),7,2) + '/' +
substring(cast(IntDateEntered as char(10)),5,2) + '/' +
left(cast(IntDateEntered as char(10)),4)
,103) DtDateEntered
FROM dbo.TestTable
Posted by Andy Todd at June 09, 2003 02:09 PM
Yuck! And SQL Server is considered to be an enterprise class database? I hope there is something similar to to_date for whatever is the equivalent of sql*loader in SQLServer world :-)
Posted by: Babu on June 9, 2003 04:45 PMOh yes, to be fair data transformation services (DTS) which ships with SQL Server definitely beats SQL*Loader into a cocked hat.
I'm not normally a big fan of GUI interfaces but that one works like a dream.
Posted by: Andy Todd on June 9, 2003 05:00 PMDTS "works like a dream" for the initial creation of your load, but you will often have problems with re-editing existing packages. The scripting support is clunky too.
We've experienced problems using DTS between service packs (and the infamous "encrypt your package" workaround).
Using bcp to load data in combination with SQL scripts has the additional advantage of fitting in happily with whatever config mgmt system you happen to use.
Posted by: Andrew Crump on June 20, 2003 02:23 AMCan any one tell whats the equivalent of to_date()
in sql server ?
My movie can not be released bcoz of this.....
Posted by: Tom cruise on August 28, 2003 10:27 AMMight be a little late helping you out but just in case you haven't found the answer yet:
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/2_005_34.htm
Kind of a clunky solution on Microsoft's part I think. Good luck.
Posted by: Don Mink on January 7, 2004 03:13 PMselect dateadd(mm, 1, cast(20010410 as char(10)))
SQLServer will implicitly convert a char value to date if it is of recognizable formats. The example above show this with the dateadd function. I do not see the need to store a date as integer but if you want it just convert the integers (4 byte) to smalldatetime (4 byte).