convert varchar to datetime - 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 2008 Forums

Transact-SQL (2008)

convert varchar to datetime

Author

Topic

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-09-11 : 03:44:09

Hi all, I am trying to output the records less than current day, however I am getting an err:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.


;WITH NewValues AS
(
select uid_person,PersonnelNumber,FirstName,LastName,CustomProperty10,del_end_date= dbo.fn__SplitValue(CustomProperty10,'--',2) from Person 
where 
---CustomProperty09 is null and 
CustomProperty10 is not null
and IsInActive =0
----and CustomProperty10 not like '%2011%'
and  ( XDateUpdated between convert(varchar,DATEADD(d,-15,getutcdate()),1) and convert(varchar,getutcdate(),1) )
)
select ---convert(datetime,rtrim(ltrim(del_end_date)),20),convert(datetime,getdate(),20),
* from NewValues p,UNSAccountB u where 
u.UID_Person = p.UID_Person
and u.AccountDisabled = 0
and p.del_end_date  like '%2012%' 
and convert(datetime,rtrim(ltrim(p.del_end_date)),21) < getdate()
order by p.del_end_date desc

Sabrina W.
Starting Member

3 Posts

Posted - 2012-09-11 : 05:08:43

In which format are your date information originally?

I would try the to_date function...if you want to insert a date manually
....(to_date(?,'dd.mm.yyyy') -7)

or the sysdate...
....(trunc(sysdate,'DD')-7)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-11 : 05:18:04

quote:
Originally posted by Sabrina W.

In which format are your date information originally?

I would try the to_date function...if you want to insert a date manually
....(to_date(?,'dd.mm.yyyy') -7)

or the sysdate...
....(trunc(sysdate,'DD')-7)

We are only on MS SQL Server here...

Too old to Rock'n'Roll too young to die.

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-09-11 : 05:18:18

quote:
Originally posted by Sabrina W.

In which format are your date information originally?

I would try the to_date function...if you want to insert a date manually
....(to_date(?,'dd.mm.yyyy') -7)

or the sysdate...
....(trunc(sysdate,'DD')-7)

Sorry i failed to mention. I am on ms sql 08.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-11 : 05:21:10

To get an answer you should give:
table structure, so we don't have to guess about data types
some sample data
wanted result in relation to the sample data

and the source of your fn_splitValue...

Too old to Rock'n'Roll too young to die.

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-09-11 : 05:29:49

quote:
Originally posted by webfred

To get an answer you should give:
table structure, so we don't have to guess about data types
some sample data
wanted result in relation to the sample data

and the source of your fn_splitValue...

Too old to Rock'n'Roll too young to die.

Here is some data.



uid_person	PersonnelNumber	FirstName	LastName	CustomProperty10	del_end_date
0334da50-9227-4e24-8d76-8ad2af61e9ba	35134	Dilek	Kinali	02.07.2012 00:00:00--03.09.2012 23:59:00	03.09.2012 23:59:00
04843f21-ed15-496f-b624-1cd4e349143e	32444	Menderes	Akdemir	31.08.2012 00:00:00--07.09.2012 23:59:00	07.09.2012 23:59:00
0496ebdc-84b5-4247-849c-256246479fdd	2091	Akin	Selçuk	10.09.2012 00:00:00--11.09.2012 23:59:00	11.09.2012 23:59:00
05420209-c40d-4a70-b688-1cf1de372782	43703	Berna	Türkmen	06.12.2010 00:00:00--31.08.2012 23:59:00	31.08.2012 23:59:00
05bcfb20-b4a5-43cd-8baa-7ec17ceab468	61766	Emine	Can	03.09.2012 00:00:00--07.09.2012 23:59:00	07.09.2012 23:59:00
05c1dbf2-780b-4ec6-82dc-be811bc72d31	53074	Ebru	Çakmak	31.08.2012 00:00:00--31.08.2012 23:59:00	31.08.2012 23:59:00
06046b8d-9447-411b-aec7-7e5a8286a286	58063	Ümit	Tekavut	27.08.2012 00:00:00--28.08.2012 23:59:00	28.08.2012 23:59:00
08daa4d9-2455-4031-a83e-ea31da2c1c90	50223	Reyhan	Akyüz	03.09.2012 00:00:00--14.09.2012 23:59:00	14.09.2012 23:59:00
0927f58e-9fb8-4e67-8d37-6e8478248347	62187	Aysegül	Yargin	10.09.2012 00:00:00--14.09.2012 23:59:00	14.09.2012 23:59:00

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 05:02:00

<<
I am trying to output the records less than current day
>>

The generic approach is

WHERE date_col<dateadd(day,datediff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources