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
APD TorontoAsked:
Who is Participating?
 
Mike EghtebasDatabase and Application DeveloperCommented:
I see.

Select Field1, Field2, Convert(varchar(10), Cast(DateField As Date), 111)  As YYYYMMDD From Table1

I tired the following, it works too. It outputs 2011-07-16.
declare @dt date;
set @dt='2011-07-16 00:03:00.000';

Select Convert(varchar(10), @dt)  As YYYYMMDD 

Open in new window


The first one outputs as 2011/07/16.
declare @dt date;
set @dt='2011-07-16 00:03:00.000';

Select Convert(varchar(10), Cast(@dt As Date), 111)  As YYYYMMDD  

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
 
APD TorontoAuthor Commented:
I think you misunderstood that I am not trying to limit my query, but rather alter my output.
0
 
PortletPaulfreelancerCommented:
>>"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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.