European date time format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

European date time format

Author

Topic

ArnoldG
Starting Member

36 Posts

Posted - 2013-12-16 : 11:28:16

Hi,
I am having an Excel cell date and time as a filter in my WHERE clause in T-SQL.
I need both the time and date to make a correct match.
I cannot adjust the Excel sheet. The date-time format in Excel is formatted dd-mm-yyyy hh-mm-sss.

I tried convert(varchar(10),getdate(),105). It is the closest that I can find in European format, but is is without time.

How can I match this Excel value in my WHERE clause in T-SQL?

Thanks for helping,

Arnold

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-16 : 12:03:59

What do you mean by matching excel in sql? Are you running a query from excel into sql server to pull a result set into excel?

It isn't clear which way you need to convert. Do you need to format a date time into a string or do you need to convert a date time string into an actual datetime in SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 07:56:51

Are you trying to generate query from Excel using Excel formulas?
In SQL Server your best bet is to sent dates in iso format which is unambiguos otherwise it can break depending on your server's language and regional settings

see
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

huskee
Starting Member

2 Posts

Posted - 2013-12-17 : 09:17:15

You can play with it and do it like this :D :D
convert(varchar(20),getdate(),105)+' '+replace(convert(varchar(8),getdate(),114),':','-');

king_fisher
Starting Member

13 Posts

Posted - 2013-12-25 : 08:11:10

try this

convert(datetime,getdate(),105)

vijay nelson

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-26 : 03:11:36

quote:
Originally posted by king_fisher

try this

convert(datetime,getdate(),105)

vijay nelson

The return type of getdate() is datetime. then whats the need of that redundant convert? i think you meant converting to varchar?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

king_fisher
Starting Member

13 Posts

Posted - 2013-12-26 : 07:37:58

if we use convert(varchar(10),getdate(),105) we get this format dd-mm-yyyy but if we use select convert(Datetime,getdate(),105) we will get dd-mm-yyyy hh-mm-sss. and he need date + time

and one more thing this we cant compare string = datetime ,so far

vijay nelson

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-26 : 11:04:20

quote:
Originally posted by king_fisher

if we use convert(varchar(10),getdate(),105) we get this format dd-mm-yyyy but if we use select convert(Datetime,getdate(),105) we will get dd-mm-yyyy hh-mm-sss. and he need date + time

and one more thing this we cant compare string = datetime ,so far

vijay nelson

nope thats not correct
By default datetime values are shown in yyyy-mm-dd hh:mi:ss format in sql server. They're stored internally as numbers
and we can compare datetime field to string literal
see this example


SELECT CASE WHEN '20131227' BETWEEN GETDATE() AND GETDATE() + 1 THEN 1 ELSE 0 END

the interpretation of date value passed as string depends on your servers language and dateformat settings. Only guaranteed formats that work always are unambiguos formats like ISO (YYYYMMDD)

see
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

king_fisher
Starting Member

13 Posts

Posted - 2013-12-26 : 23:34:12

why not you run this on your sql server

select convert(nvarchar(max),getdate(),103)

select convert(datetime,getdate(),103)

vijay nelson

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 04:03:10

quote:
Originally posted by king_fisher

why not you run this on your sql server

select convert(nvarchar(max),getdate(),103)

select convert(datetime,getdate(),103)

vijay nelson

for convert to work as intended you need to pass an EXPLICIT style always. This is not useful unless you're sure that input date values through varchar variable has a consistent format which is not always possible.
So my point was always try to pass dates in unambiguos format rather than relying upon server settings or an explicit CONVERT using a given (static) style factor.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-06 : 12:26:13

quote:
Originally posted by king_fisher

why not you run this on your sql server

select convert(nvarchar(max),getdate(),103)

select convert(datetime,getdate(),103)

vijay nelson

I'm not sure what you are trying illustrate, but I think there is disconnect on what the FORAMT option is for. If you are converting from and to the same type the format is irrelevant:

select convert(datetime,getdate(),103)
select convert(datetime,getdate(),105)
select convert(datetime,getdate(),106)
select CAST(GETDATE() AS DATETIME)

Now, if you are converting from one type to another, then the format makes a difference.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources