March 30, 2015 at 2:01 pm
Hi,
I need to compare the date column. The date column is 2012-01-22 in this format.
So I need to convert getdate() into this format yyyy-mm-dd.
What is the style number I have to use?
I used the link http://www.sql-server-helper.com/tips/date-formats.aspx but I didn't see this format
March 30, 2015 at 2:12 pm
First you don't have to convert the getdate() in any format. See the following example:
declare @a date
select @a = '2012-10-22'
if @a > GETDATE()
select 111
else
select 222
But for your reference here is the conversion:
12 112 ISO yymmdd yyyymmdd
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm
Hope this work.
April 2, 2015 at 6:53 am
You don't need to convert the result of GETDATE() to some VARCHAR value to compare it with a date column. Declare a DATE variable and assign it the value of GETDATE.
Something like below should help
DECLARE@CurrentDate DATE
SET@CurrentDate = GETDATE()
SELECT*
FROMTableName
WHEREColumnName = @CurrentDate
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 2, 2015 at 10:20 am
Use 'YYYYMMDD', since that format always works correctly in SQL Server; YYYY-MM-DD doesn't work correctly if the local settings are for dmy instead of mdy.
WHERE table_column = CONVERT(varchar(8), GETDATE(), 112)
Never use such functions or computations on the table column, only on fixed values such as GETDATE() or local variables.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 2, 2015 at 11:37 am
If you have a date format (Date, DateTime, SmallDateTime, et. al.) these values will compare as SQL server stores them and knows what they are. The value you see from a select, is what is defined for humans.
If you have a VARCHAR I would suggest converting to a date format of some sort before compare.
As to formatting output, it is suggested that you use the front end to format the date.
April 2, 2015 at 12:36 pm
djj (4/2/2015)
If you have a VARCHAR I would suggest converting to a date format of some sort before compare.
A bad idea. Not necessary and could potentially force a conversion on the table column, which is what you want to avoid at all costs.
SQL will always convert a varchar to the appropriate data type to compare to the column without having to ever convert the column itself.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply