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;
substring(cast(IntDateEntered as char(10)),7,2) + '/' +
substring(cast(IntDateEntered as char(10)),5,2) + '/' +
left(cast(IntDateEntered as char(10)),4)
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 :-)
Oh 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.
DTS "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.
Can any one tell whats the equivalent of to_date()
in sql server ?
My movie can not be released bcoz of this.....
Might be a little late helping you out but just in case you haven't found the answer yet:
Kind of a clunky solution on Microsoft's part I think. Good luck.
select 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).