Date format in T-SQL
-
My dear friends, I’m battling with a major issue in T-SQL. I want to specify the format of a DateTime variable. In VB there is a command called ‘DateSerial’ that you can use to specify each value (Day, Month and Year) of the Date variable, but there is no such function in T-SQL. I live in South-Africa, and the date here is in the following order: Day, Month, Year. If I set a DateTime variable in this order - (
SET @Date = ‘14/2/2007’) - the following error occurs: ‘The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.’ How can I solve this problem? Thanks, WerriesA programmer's life is good... or is it?? Ek dink nie so nie!
-
My dear friends, I’m battling with a major issue in T-SQL. I want to specify the format of a DateTime variable. In VB there is a command called ‘DateSerial’ that you can use to specify each value (Day, Month and Year) of the Date variable, but there is no such function in T-SQL. I live in South-Africa, and the date here is in the following order: Day, Month, Year. If I set a DateTime variable in this order - (
SET @Date = ‘14/2/2007’) - the following error occurs: ‘The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.’ How can I solve this problem? Thanks, WerriesA programmer's life is good... or is it?? Ek dink nie so nie!
Hi there, Thanks for those who had a look at my problem, but I've solved it. Just one simple sample on a previous post on this forum gaved me the answer.
DECLARE @QAZ AS DATETIME SET DATEFORMAT dmy;SET @QAZ = '14-02-2007' print @QAZResult: Feb 14 2007 12:00AMA programmer's life is good... or is it?? Ek dink nie so nie!
-
My dear friends, I’m battling with a major issue in T-SQL. I want to specify the format of a DateTime variable. In VB there is a command called ‘DateSerial’ that you can use to specify each value (Day, Month and Year) of the Date variable, but there is no such function in T-SQL. I live in South-Africa, and the date here is in the following order: Day, Month, Year. If I set a DateTime variable in this order - (
SET @Date = ‘14/2/2007’) - the following error occurs: ‘The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.’ How can I solve this problem? Thanks, WerriesA programmer's life is good... or is it?? Ek dink nie so nie!
I strongly recommend you use the ISO date format
yyyyMMdd, interpretation of which is not subject to the locale or language settings of your SQL Server installation or the connection. So I would useSET @Date = '20070214'.Stability. What an interesting concept. -- Chris Maunder