need help with an update query

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)

need help with an update query

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-11-06 : 04:28:36

Hi

i have some date in a column (see below)
A lot of the date are missing leading zeros and I need to add the leading zeros so that an APP can read them properly.
but I can't figure out how to do the query. It might be soemthing simple but my mind had gone blank.
The first one is 1999-4-1 this is yyyy-m-d
I need to update it to yyyy-mm-dd

also I should point out the field is varchar (10)

Hope that makes sense!
1999-4-1
1999-4-1
1999-5-9
1999-4-6
1999-4-19
1999-11-28
1999-5-2
1999-04-24
1999-4-25
1999-3-10
1998-11-5
1999-4-25
1999-5-16
1999-4-28
1999-3-9
1999-5-27
1999-4-21
1998-11-5
1999-3-4
1999-3-24
1999-4-12
1999-5-9
1999-4-29
1999-3-24
1999-5-10
1999-5-25
1999-5-16
1999-5-27
1999-3-28

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-06 : 04:43:02

What is the datatype of the date column?

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-11-06 : 04:44:34

SOrry i just updated the original post with the data type varchar (10)

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-06 : 04:51:59

Hi

IS this fine for you
SELECT CONVERT(VARCHAR(30),CONVERT(DATETIME,'1999-4-1'),111)

-------------------------
R...

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-11-06 : 04:58:41

Thanks but I don't want to convert it to a datetime field.
I still want it to be Varchar (10) as the application requires that.
I just need to look for where a leading zero is missing and then add one.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-06 : 04:59:56

quote:
Originally posted by icw

SOrry i just updated the original post with the data type varchar (10)

1 Always use proper DATETIME datatype to store dates
2 Let Front end application do the formatation (ASP in your case)
3 Use this in ASP Page
YEAR(Date()) & "-" & Pd(Month(date()),2) & "-" & Pd(DAY(date()),2)

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-11-06 : 05:02:37

I really appreciate your help many thanks to you.
But its not my Application and is hard coded so I don't have any choice.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-06 : 05:05:19

quote:
Originally posted by icw

Thanks but I don't want to convert it to a datetime field.
I still want it to be Varchar (10) as the application requires that.
I just need to look for where a leading zero is missing and then add one.

To store the data always use proper datatype…formation we can do it in display part what madhi is said.

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-06 : 05:23:38

quote:
Originally posted by icw

I really appreciate your help many thanks to you.
But its not my Application and is hard coded so I don't have any choice.

So, you dont have any chance to modify the ASP page?
Do you have access to modify the procedure?

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-11-06 : 05:23:55

Thank you but I don't have a choice. I am not designing the software

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-11-06 : 05:25:02

The software is a service-desk application called HEAT from Frontrange Solutions.
It uses Varchar (10) for date fields.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-06 : 05:28:33

quote:
Originally posted by icw

The software is a service-desk application called HEAT from Frontrange Solutions.
It uses Varchar (10) for date fields.

The only way is updating your table

update your_table
set date_col=convert(varchar(10),cast(date_col as datetime),120)

Now goto the page and see

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-11-06 : 05:33:29

I ran this update but the data hasn't changed
The table is called Profile the field is called tillsindate

update profile
set tillsindate = left(convert(varchar,tillsindate,120),10)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-06 : 05:38:33

I already edited my reply

Here is the one

update your_table
set date_col=convert(varchar(10),cast(date_col as datetime),120)

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-11-06 : 05:41:28

Hi

both will work

SELECT CONVERT(VARCHAR(10),CONVERT(DATETIME,'1999-4-1'),111)

SELECT CONVERT(VARCHAR(10),CAST('1999-4-1' AS DATETIME),120)

-------------------------
R...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-06 : 05:46:04

as what rajdaksha suggested, convert to datetime and then convert back to varchar but use style 121


update your_table
set    datecol = convert(varchar(10), convert(datetime, datecol, 121), 121)

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-11-06 : 06:07:43

Thanks guys that works great!
Have a nice weekend!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-06 : 06:12:40

The last (or not the last) question could be:
What is about data coming new into the table - is the problem still the same in new inserted records?
Is it possible to have a trigger for that?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources