Converting dates - to dates

[SirDrinksalot]

Hi all,

I have a wee problem which hopefully someone can help me with. I have a datetime field with the format dd/mm/yyyy hh:mm:ss, and i need to extract the date part only, but also rearrange it so that it is of format YYMMDD. This i have achieved by using the convert and datepart functions. However, as a result of this, the date returned is now in a varchar format. Unfortunately the client wants the return value to be of type Datetime - is this possible?

To help anyone who's interested, here is the code i ave currently written:

(Select
right(convert(varchar(4),datepart(year,inf.feature_insp_date)),2)
+ (case len(datepart(month,inf.feature_insp_date))
when 1 then '0' else '' end) + convert(varchar(2),datepart(month,inf.feature_insp_date))
+ (case len(datepart(day,inf.feature_insp_date))
when 1 then '0' else '' end) + convert(varchar(2),datepart(day,inf.feature_insp_date))
from engineer.inspection_feature inf
where inf.plot_number = def.plot_number) as inspection_date,

Hopefully someone can see through the haze and open my eyes!

 

[checkai]

if they want a datetime then they should be able to format it on their end any way the want it...

"...we both know I'm training to become a cagefigher...see what happens if you try 'n hit me..."

 

[vongrunt]

That said, native date/time types (datetime, smalldatetime) have NO FORMAT.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[Catadmin]

Even if you convert it to a smalldatetime after you've done it, it'll either come out wrong and/or append a 12:00 AM type of time at the end of it.

I would check with your client to see what application they are using to pull up this info and see if you can put some sort of formating or mask on their end of it to filter it the way they want.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[SQLBill]

As Vongrunt says, DATETIME has no real format...from the BOL:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

So as I always say, Date/time values are STORED as DATETIME datatype, but it's how they are DISPLAYED that is up to the end-user. And they are really displayed as a string (VARCHAR or CHAR, whichever you choose).

-SQLBill

Posting advice: FAQ481-4875

 

[rccoates]

Try using convert statement

example
convert (datetime,Mytime,112)

 

[SQLBill]

rccoates,

Have you actually tried your suggestion? (not being sarcastic, just curious).

I tried it (SELECT CONVERT(DATETIME, GETDATE(), 112)) and it returned '2005-04-04 16:04:52.660'.

That's because the value returned by DATETIME is based on the collation setting, not the STYLE.

-SQLBill

Posting advice: FAQ481-4875