SQL Server Date Problem
-
Hi Geeks, I am quite new to SQL Server.Well I have a problem.. Could someone help me ?? This is my Query: sql="SELECT Id,Name, Age,City, AdmDate FROM IP LEFT JOIN MDoc ON IP.DoId=MDoc.DoId WHERE AdmDate='11/15/2003' ORDER BY IP_Id" Now in the IP table there are records containing date values i.e. AdmDate='15/11/2003' ;these date values r in small date format with precision to seconds. Now I want All the records of a paticular date irrespective of their time Value. -SimSan
-
Hi Geeks, I am quite new to SQL Server.Well I have a problem.. Could someone help me ?? This is my Query: sql="SELECT Id,Name, Age,City, AdmDate FROM IP LEFT JOIN MDoc ON IP.DoId=MDoc.DoId WHERE AdmDate='11/15/2003' ORDER BY IP_Id" Now in the IP table there are records containing date values i.e. AdmDate='15/11/2003' ;these date values r in small date format with precision to seconds. Now I want All the records of a paticular date irrespective of their time Value. -SimSan
I typically use
SELECT Id,Name, Age,City, AdmDate
FROM IP LEFT JOIN MDoc
ON IP.DoId=MDoc.DoId
WHERE AdmDate >= '2003-11-15' AND
AdmDate < DATEADD( Day, 1, '2003-11-15' )
ORDER BY IP_IdOf course, typically I use this is in a stored procedure where the literal date is replaced by a parameter, where using DATEADD makes a bit more sense.
-
I typically use
SELECT Id,Name, Age,City, AdmDate
FROM IP LEFT JOIN MDoc
ON IP.DoId=MDoc.DoId
WHERE AdmDate >= '2003-11-15' AND
AdmDate < DATEADD( Day, 1, '2003-11-15' )
ORDER BY IP_IdOf course, typically I use this is in a stored procedure where the literal date is replaced by a parameter, where using DATEADD makes a bit more sense.
-
This works fine...Thanks for the tip. Well...I wanted to clarify if SQL Server treats Dates in American Date format.If it does,why this date format ('yyyy-mm-dd') is in use. -SimSan
Because I'm in the UK ;) SQL Server interprets literal dates in queries using the user's default locale - even in stored procedures, IIRC. You could write '10/11/2003' to be interpreted as October 11th, but if your user is using the English (UK) locale, it will be interpreted as 10 November. "SQL Server evaluates datetime constants at run time. A date string that works for the date formats expected by one language may be unrecognizable if the query is executed by a connection using a different language and date format setting." Using Date and Time Data[^] The ISO format yyyy-mm-dd is supposed to be supported in all locales with the same meaning. On re-reading Writing International Transact-SQL Statements[^], it actually looks like you should use unseparated dates (format yyyymmdd) to ensure correct interpretation. So I've been getting it wrong. :-O
-
Hi Geeks, I am quite new to SQL Server.Well I have a problem.. Could someone help me ?? This is my Query: sql="SELECT Id,Name, Age,City, AdmDate FROM IP LEFT JOIN MDoc ON IP.DoId=MDoc.DoId WHERE AdmDate='11/15/2003' ORDER BY IP_Id" Now in the IP table there are records containing date values i.e. AdmDate='15/11/2003' ;these date values r in small date format with precision to seconds. Now I want All the records of a paticular date irrespective of their time Value. -SimSan
-
Because I'm in the UK ;) SQL Server interprets literal dates in queries using the user's default locale - even in stored procedures, IIRC. You could write '10/11/2003' to be interpreted as October 11th, but if your user is using the English (UK) locale, it will be interpreted as 10 November. "SQL Server evaluates datetime constants at run time. A date string that works for the date formats expected by one language may be unrecognizable if the query is executed by a connection using a different language and date format setting." Using Date and Time Data[^] The ISO format yyyy-mm-dd is supposed to be supported in all locales with the same meaning. On re-reading Writing International Transact-SQL Statements[^], it actually looks like you should use unseparated dates (format yyyymmdd) to ensure correct interpretation. So I've been getting it wrong. :-O