Tip on Datetime and SQL

A lot of non-US developers have troubles with date andling and SQL Server.

If you send to SQL 01/10/2003 it could be interpreted as October 1 2003 or January 10 2003 depend where you live.

I don't find .Net so easy on this subject. So this what I do to send the right format to SQL, and it will be interpreted correctly whatever the settings you have.

This is based on the fact that SQL accept a date like this '20041001' (YYYYMMDD format) . So whatever your settings, this will be all the time seen as October 1st 2004.

If you need to send your date as a parameter for a stored procedure, you must transform your date like this (VB code):

MyDateParameter = CStr(MyDate.Date.GetDateTimeFormats("d")(4).Replace("-", "")))

The method GetDateTimeFormats returns an array of all posible date formats. So here I choose the 4th element returning a date as 2004-10-01 and I just remove the '-' character.

I tried different options, and I didn't found the exact SQL format like '20041001', that's why I choose this option.

 

 

2 Comments

  • First, if you're going to send it as a string just use MyDate.ToString("yyyyMMdd").



    Second, and for the milionth time, do not build SQL query strings, use parameterized commands and give it a DateTime structure parameter. ADO will take care of proper formatting and escaping (for string parameters).

  • Mr Pisk is right. On my computer, the separator is a '.' and the '-' is array[5]



    yyyymmdd will always work.

Comments have been disabled for this content.