string to smalldatetime - 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 2005 Forums

Transact-SQL (2005)

string to smalldatetime

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-10-27 : 06:46:47

I would like to convert the following two variables to smalldatetime

declare @date1 varchar(20)
declare @date2 varchar(20)

@date1 = '190818'
@date2 = '90818'

This is what i am doing but I do not get the correct format:

Query1

set dateformat 'dmy'
select convert(smalldatetime, @date1)

Query2

set dateformat 'dmy'
select convert(smalldatetime, @date2)

Result of Query 1:
2019-08-18 00:00:00

The result should show:
19-08-2018

Result of Query 2 is error

Any thoughts please?
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-27 : 06:54:39

when you specify the date with /, it is assume as ISO format which is YYYYMMDD

for dmy, you will at least required a / or - as separator for the date like DD/MM/YYYY

try this


set dateformat dmy

select	convert(datetime, '190818'), convert(datetime, '19/08/18'), convert(datetime, '19-08-18')

KH
[spoiler]Time is always against us[/spoiler]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-10-27 : 06:58:06

What if the parameter is as specified withot '/' or '-' ?
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-10-27 : 07:07:37

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources