How to get SQL Server Date in its native format?

Question

I need to get a Date from an SQL Server Database (SQL Server 2000), the date is stored as such:

2009-09-30 00:00:00.000

I want to pass this date into a Classic ASP function to use this date in the Database somewhere else, however when I get the date, it converts in my ASP code to a locale-dependant version, such as:

30/09/2009

I just want the date as-is as formatting the date so it is correct again seems unnecessary, although if this the only way then fine - is there any way to treat it as string data so it stays like that, so it can be inserted back into the database as the same date, without needing conversion?

Answer 1

Here's a complete list of ways: http://www.sql-server-helper.com/tips/date-formats.aspx

As a note, the storage is actually two integers. The first is the number of days before or after Jan 1, 1900 and the other is number of milliseconds since midnight.

Answer 2

The date is not stored as '2009-09-30 00:00:00.000', it's stored as an 8-byte number where the first 4 bytes are days since jan 1, 1900 and the other 4 bytes are milliseconds in the date.

To get to your format, use convert(varchar, dt, 121). To get the raw format use convert(binary(8), dt)

EDIT: After your question was edited it appears that what you really want is to be able to perform the conversion date -> string -> date.

For that you can use @s = convert(varchar, @dt, 121); @dt = convert(datetime, @s, 121). All other formats would probably work as well, as long as they are consistently applied in both directions.

Answer 3

Here is a function for this ...

'**********************************************************************************************************
'' @SDESCRIPTION:   Gets an independent sql representation for a datetime string standardised by ISO 8601 
'' @DESCRIPTION:    If you want to create a sql statement containing a date query in the where clause,
''      use this function to create a datetime object.
''      EXAMPLE:
''      In Sql Server: Declare @timeTable TABLE (name int, starttime datetime)
''      In VBScript: sql = "SELECT * FROM timeTable WHERE starttime = " & date.toMsSqlDateFormat(cDate('2007-01-01 15:30:00'))
''      Results in: SELECT * FROM timeTable WHERE starttime = cast('2006-01-01T15:30:00' as datetime)
''      NOTE: only for MS SQL Server
'' @PARAM:    dat [date]: the date/time you want to cast
'' @RETURN:   [string] the formatted date string
'**********************************************************************************************************
public function toMsSqlDateFormat(dat)
    if dat = empty then exit function
    toMsSqlDateFormat = "cast('" & year(dat) & "-" & lib.custom.forceZeros(month(dat), 2) & "-" & _
         padLeft(day(dat), 2, "0") & "T" & padLeft(hour(dat), 2, "0") & ":" & _
         padLeft(minute(dat), 2, "0") & ":" & padLeft(second(dat), 2, "0") & "' as datetime)"
end function

'******************************************************************************************************************
'' @SDESCRIPTION:   right-aligns a given value by padding left a given character to a totalsize
'' @DESCRIPTION:    example: input: <em>22</em> -> output: <em>00022</em> (padded to total length of 5 with the paddingchar 0)
'' @PARAM:    value [string]: the value which should be aligned right
'' @PARAM:    totalLength [string]: whats the total Length of the result string
'' @PARAM:    paddingChar [string]: the char which is taken for padding
'' @RETURN:   [string] right aligned string.
'******************************************************************************************************************
public function padLeft(value, totalLength, paddingChar)
    padLeft = right(clone(paddingChar, totalLength) & value, totalLength)
end function

public function clone(byVal str, n)
    for i = 1 to n : clone = clone & str : next
end function

Answer 4

If you just want to retrieve a date and subsequently use it in another query, you're better off not converting it to a string at all. In that way you don't need to worry about what formats are used during the conversion.

To do so you need to use parametered queries. You can google for this, but in classic ASP with VB it will look something like:

' Retrieval
...
Set objCommand = CreateObject("ADODB.Command")
...
objCommand.CommandText = "SELECT SomeDate FROM SomeTable"
...
Set objRS = objCommand.Execute
...
dtSomeDate = objRS("SomeDate").Value ' This is a Date object


' Write date to db
...
Set objCommand = CreateObject("ADODB.Command")
...
objCommand.CommandText = "INSERT INTO SomeTable (...,SomeDate) VALUES (...,?)"
Set objParam = objCommand.CreateParameter("SomeDate", adDateTime, adParamInput, dtSomeDate)
...
objCommand.Execute

Answer 5

I know this is an old thread, but I thought it might be relevant anyway. Is it possible to set a default return format for all dates which are not implicitly formatted? For example force all dates to be returned in the form of "CCYY-mm-dd HH:ii:ss" regardless of locale.