CODEWITHSUNDEEP
sqlsql-servert-sqldatetype-conversion
Anonymous
Anonymous

Reputation: 440

SQL - temporarily set default date when parsing time as datetime in SSMS

if I do

SELECT CAST('11:30 pm' AS datetime)

I get 1900-01-01 23:30:00.000

but if I do 

SELECT PARSE('11:30 PM' AS datetime using 'en-gb')

I get 2019-08-20 23:30:00.000

i.e. parse gives today's date.

Is it possible to modify the default date that parsing a time value gives to 1900-01-01 (similar to cast)?

The reason I want this is because I'm looping through a large number of columns and want to determine whether or not they should be set to dates, times or datetimes so if every date in a datetime column is 1900-01-01 I can certain that i need to convert it to a time column (because I will never deal with the date 1900-01-01) but there is a possibility that all the dates in a column are set to today's date and I shouldn't be removing the date section of the column.

For example one column could be:

11:01
11:30
11:40

but another column could be

20-08-2019 11:00
20-08-2019 11:05
20-08-2019 11:10

I want to run the same procedure against both columns to get them in the right format. So the first column should stay as a time column and the 2nd should be a datetime column.

As I say I have several columns so I don't want to manually look through each one to determine if it should be a datetime or time but if I could have the date set to 1900-01-01 when I parse a time to datetime (similar to cast) I'll be able to see which columns should have the date part removed.

I have something similar to deal with datetimes where the time part should be removed: I simply check if there exists at least one time which isn't 00:00 and if there isn't I'll convert it to do a date.

I need to use PARSE as I'm unsure of the formats the dates will be in and PARSE is significantly more flexible than CONVERT or CAST (from my experience).

Upvotes: 0

Views: 263

Answers (3)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

As I saw , it may not be possible because PARSE() uses .NET with DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal for date and/or time parsing as fixed setting.

You can use SET LANGUAGE to set a specific language/culture when using CAST or CONVERT.

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

If you are only looking for value '1900-01-01' as output, you can simply cast as date as below-

SELECT CAST('11:30 pm' AS date)

Output is-

1900-01-01

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

I'm not sure why you would want a datetime value from 1900, but you can use:

select convert(datetime, PARSE('11:30 PM' AS time using 'en-gb'))

I would be content with a time value:

select PARSE('11:30 PM' AS time using 'en-gb')

Upvotes: 1

Related Questions