asked on

Converting Date in SELECT

Hi Experts,

Within MSSQL I have a fldDate that has the following value 2011-07-16 00:00:00.000, and I need to select this as 2011-07-16

Both Left([fldDate],11) and Left([fldDate],10) do not give me the correct output

fldDate is a datetime field

Any help will be appreciated

Mike Eghtebas

Select Field1, Field2, DateField From Table1
Where DateField >= '2011-07-16' AND DateField <= '2011-07-16'

if 2011-07-16 00:00:00.000 then both are true

if 2011-07-16 00:00:02.000 then both are true

APD Toronto

ASKER

I think you misunderstood that I am not trying to limit my query, but rather alter my output.

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

>>"fldDate is a datetime field"

>>"Both Left([fldDate],11) and Left([fldDate],10) do not give me the correct output"

It will make life easier to know that date/datetime/datetime2/smalldate/time are NOT STORED they way you see them. All of those data types are stored a special numbers.

We humans get to see those fields through a "default format" like 2011-07-16 00:00:00.000
but that does not make the field a varchar, it just looks that way

Prior to SQL Server 2012 you can use CONVERT() with a series of numeric "styles" to control the output of datetime information. See: SQL Server Date Styles (formats) using CONVERT()

From MS SQL 2012 on you can use FORMAT() instead which is more intuitive IMHO

e.g.
select format(getdate() , 'yyyy-MM-dd')

select format( [fldDate] , 'yyyy-MM-dd')

nb the 'yyyy-MM-dd' parameter is case sensitive