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 TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 TorontoSoftware DeveloperAuthor Commented:
I think you misunderstood that I am not trying to limit my query, but rather alter my output.
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
>>"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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.