• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

How do I enter the date into a column in a specific way without having them add up?

I'm trying to insert a date plus "01"  into my database in a very specific format. For example, if I were inserting this today I would insert 2008103001. I've tried the code below, but it simply adds my values together and would put 2050 into the field instead what I want. I'm sure it's something simple, but I'm too stupid to figure it out.

UPDATE    table
SET thisfield = YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE()) + 01
0
cjhhiv
Asked:
cjhhiv
  • 3
  • 2
  • 2
  • +1
1 Solution
 
BrandonGalderisiCommented:
The below query will return TOMORROW in a DATE only format with NO TIME.

select convert(datetime, convert(varchar(10), getdate()+1, 101), 101)
0
 
rpkhareCommented:
What is the Datatype of the column in which you are inserting this value? I guess DateTime will not accept what you are desiring. You need to take Varchar.
0
 
sdstuberCommented:
what do you mean by a date plus "01"

"01" is a string,  a date is a date, it doesn't make sense to add them together.

Do you mean add one hour to a date?

then do something like this...

update yourtable set thisfield = dateadd(hh,1,GetDate())
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
BrandonGalderisiCommented:
Optionally

select convert(char(8), getdate(),112)

will yield yyyymmdd


select convert(varchar(10), getdate(),112)  + '01'

will yield yyyymmdd01
0
 
BrandonGalderisiCommented:
You REALLY don't want to do this though.  You should store dates as dates and handle formatting when it's time to display it.
0
 
cjhhivAuthor Commented:
Thanks "select convert(varchar(10), getdate(),112)  + '01'" was exactly what I was looking for!
0
 
cjhhivAuthor Commented:
Thanks all. I should have clarified, which BrandonGalderisi picked up on. I wanted to insert a number into the field that was based on a date. The number needed to have '01' added to the end. It was an nvarchar(50) field.

Thanks.
0
 
sdstuberCommented:
I will second BrandonGalderisi's warning.  Store dates as dates,  it removes all ambiguity and makes operations more efficient and less error prone.

If it's at all feasible to do so,  modify that table so it's datatypes are correct
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now